﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindowsFormsApp1.SQlite;

namespace WindowsFormsApp1
{
    public class DbOperate : IDisposable
    {
        /// <summary>
        /// 实列
        /// </summary>
        private static DbOperate _instance;
        /// <summary>
        /// 实列锁
        /// </summary>
        private static object _objectLock = new object();

        /// <summary>
        /// 配置参数-单列对象
        /// </summary>
        public static DbOperate Instance
        {
            get
            {
                if (_instance == null)
                {

                    lock (_objectLock)
                        if (_instance == null) _instance = new DbOperate();
                }
                return _instance;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        private DbOperate()
        {
            if (!File.Exists(Path.Combine(System.Windows.Forms.Application.StartupPath, "PingTechDB.sqlite")))
            {
                CreateDatabase();
            }
        }
        public void Close()
        {
            SqlLiteLongConn.CloseConnecton();
        }
        /// <summary>
        /// 创建库
        /// </summary>
        private void CreateDatabase()
        {
            int phone_length = 20;
            //安保人员信息
            string creatstr = @"CREATE TABLE PersonInfo ( 
                                    guid          VARCHAR (64) PRIMARY KEY,
                                    name        VARCHAR (64),
                                    cardNo      VARCHAR (20),
                                    phone        VARCHAR (20),
                                    email        VARCHAR (64),
                                    gender        VARCHAR (8),
                                    birth        VARCHAR (20),
                                    address        VARCHAR (200),
                                    photo       VARCHAR (200),
                                    height       VARCHAR(8),
                                    
                                    lastPosition    VARCHAR (64),
                                    lastCompany    VARCHAR (64),
                                    lastIndusty     VARCHAR (64),
                                    major     VARCHAR (128),
                                    school         VARCHAR (128),
                                    highestDegree        VARCHAR (64)
                                        
                                );";
            if(!ifExistTable("PersonInfo"))
                SqlLiteLongConn.ExecuteSql(creatstr);

            //打卡记录信息
            creatstr = @"CREATE TABLE WorkHistory ( 
                                    guid          VARCHAR (64) PRIMARY KEY,
                                    personId       VARCHAR (64),
                                    startTime    VARCHAR (20),
                                    endTime        VARCHAR (20),
                                    position         VARCHAR (64),
                                    department     VARCHAR (64),      
                                    company             VARCHAR (64),
                                    industy     VARCHAR (64),
                                    workDescription  VARCHAR (300),
                                    orderNum           INTEGER

                                );";
            if (!ifExistTable("WorkHistory"))
                SqlLiteLongConn.ExecuteSql(creatstr);
        }

        bool ifExistTable(string tableName)//判断表是否存在
        {
            string sql = "select * from sqlite_master where name='" + tableName + "'";
            DataSet ds = SqlLiteLongConn.Query(sql);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return false;
            return true;
        }


        /// <summary>
        /// 获取guid
        /// </summary>
        /// <returns></returns>
        public string GuidTo16String()
        {
            string guid = Guid.NewGuid().ToString("N");
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < guid.Length; i += 2)
            {
                sb.Append(guid[i]);
                if (sb.Length >= 16)
                    break;
            }
            string str = sb.ToString();
            return str;
        }
               

        public void Dispose()
        {
            Close();
        }
        
        #region 人员信息
        public bool AddOrUpdatePerson(PersonModel record)
        {
           try
           {
                if (IsExistPerson(record.guid))
                {
                    //更新
                    if (!DeleteWorkHistory(record.guid)) return false;
                    if (!UpdatePerson(record)) return false;
                    if (!addWorkHistory(record.guid, record.workHistoryModels)) return false;
                    return true;
                }
                else
                {
                    //增加
                    return AddPerson(record);
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            
        }
        /// <summary>
        /// 添加人员
        /// </summary>
        private bool AddPerson(PersonModel record)
        {
            string InsertSqlstr = string.Empty;
            string guid = record.guid == "" ? GuidTo16String(): record.guid;
            try
            {
                InsertSqlstr = @" Insert into PersonInfo(
                                    guid,
                                    name ,
                                    cardNo,
                                    phone,
                                    email,
                                    gender ,
                                    birth ,
                                    address  ,
                                    photo ,
                                    height ,
                                    lastPosition  ,
                                    lastCompany ,
                                    lastIndusty   ,
                                    major ,
                                    school ,
                                   highestDegree) VALUES ";
                InsertSqlstr += "( '" + guid + "',";
                InsertSqlstr += " '" + record.name + "',";
                InsertSqlstr += " '" + record.cardNo + "',";
                InsertSqlstr += " '" + record.phone + "',";
                InsertSqlstr += " '" + record.email + "',";
                InsertSqlstr += " '" + record.gender + "',";
                InsertSqlstr += " '" + record.birth + "',";
                InsertSqlstr += " '" + record.address + "',";
                InsertSqlstr += " '" + record.photo + "',";
                InsertSqlstr += " '" + record.height + "',";
                InsertSqlstr += " '" + record.lastPosition + "',";
                InsertSqlstr += " '" + record.lastCompany + "',";
                InsertSqlstr += " '" + record.lastIndusty + "',";
                InsertSqlstr += " '" + record.major + "',";
                InsertSqlstr += " '" + record.school + "',";
                InsertSqlstr += " '" + record.highestDegree + "'";
                InsertSqlstr += ")";

                if (SqlLiteLongConn.ExecuteSql(InsertSqlstr) <= 0)
                {
                    return false;
                }
                return addWorkHistory(guid, record.workHistoryModels);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private bool addWorkHistory(string personID, List<WorkHistoryModel> workHistoryModels)
        {
            
            try
            {
                for(int i=0; i< workHistoryModels.Count; i++)
                {
                    WorkHistoryModel record = workHistoryModels[i];
                    string InsertSqlstr = string.Empty; 
                    InsertSqlstr = @" Insert into WorkHistory(
                                    guid,
                                    personId ,
                                    startTime,
                                    endTime,
                                    position,
                                    department ,
                                    company ,
                                    industy,
                                    workDescription  ,
                                    orderNum) VALUES ";
                    InsertSqlstr += "( '" + GuidTo16String() + "',";
                    InsertSqlstr += " '" + personID + "',";
                    InsertSqlstr += " '" + record.startTime + "',";
                    InsertSqlstr += " '" + record.endTime + "',";
                    InsertSqlstr += " '" + record.position + "',";
                    InsertSqlstr += " '" + record.department + "',";
                    InsertSqlstr += " '" + record.company + "',";
                    InsertSqlstr += " '" + record.industy + "',";
                    InsertSqlstr += " '" + record.workDescription + "',";
                    InsertSqlstr += " '" + record.order.ToString() + "'";
                    InsertSqlstr += ")";
                    if (SqlLiteLongConn.ExecuteSql(InsertSqlstr) <= 0)
                    {
                        return false;
                    }
                }
                
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新人员
        /// </summary>
        public bool UpdatePerson(PersonModel record)
        {
            string InsertSqlstr = string.Empty;
            try
            {
                InsertSqlstr = @" Update PersonInfo Set name = ";
                InsertSqlstr += "'" + record.name + "', cardNo=";
                InsertSqlstr += " '" + record.cardNo + "',phone=";
                InsertSqlstr += " '" + record.phone + "',email=";
                InsertSqlstr += " '" + record.email + "',gender=";
                InsertSqlstr += " '" + record.gender + "',birth=";
                InsertSqlstr += " '" + record.birth + "',address=";
                InsertSqlstr += " '" + record.address + "',photo=";
                InsertSqlstr += " '" + record.photo + "',height=";
                InsertSqlstr += " '" + record.height + "',lastPosition=";
                InsertSqlstr += " '" + record.lastPosition + "',lastCompany=";
                InsertSqlstr += " '" + record.lastCompany + "',lastIndusty=";
                InsertSqlstr += " '" + record.lastIndusty + "',major=";
                InsertSqlstr += " '" + record.major + "',school=";
                InsertSqlstr += " '" + record.school + "',highestDegree=";
                InsertSqlstr += " '" + record.highestDegree + "'";
                InsertSqlstr += " where guid = ";
                InsertSqlstr += " '" + record.guid + "';";

                if (SqlLiteLongConn.ExecuteSql(InsertSqlstr) <= 0)
                {
                    return false;
                }
                
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// false 没有记录。
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsExistPerson(string id)
        {
            string selectSqlstr = string.Empty;
            try
            {
                selectSqlstr = @" select guid from PersonInfo where guid='" + id + "'";
                DataSet ds = SqlLiteLongConn.Query(selectSqlstr);
                if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                    return false;
                else return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// 根据名称获取人员信息
        /// </summary>
        /// <returns></returns>
        public List<PersonModel> GetPersonByName(string name = "", string cardNo="")
        {
            List<PersonModel> listRequest = new List<PersonModel>();
            string selectSqlstr = "select * from PersonInfo ";
            if (name != "")
                selectSqlstr += " where name like '%" + name + "%';";
            else if(cardNo != "")
                selectSqlstr += " where cardNo like '%" + cardNo + "%';";

            try
            {
                
                DataSet ds = SqlLiteLongConn.Query(selectSqlstr); 
                if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                    return listRequest;
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    PersonModel recordModel = new PersonModel();
                    recordModel.guid = row["guid"].ToString();
                    recordModel.name = row["name"].ToString();
                    recordModel.cardNo = row["cardNo"].ToString();
                    recordModel.phone = row["phone"].ToString();
                    recordModel.email = row["email"].ToString();
                    recordModel.gender = row["gender"].ToString();
                    recordModel.birth = row["birth"].ToString();
                    recordModel.address = row["address"].ToString();
                    recordModel.photo = row["photo"].ToString();
                    recordModel.height = row["height"].ToString();
                    recordModel.lastPosition = row["lastPosition"].ToString();
                    recordModel.lastCompany = row["lastCompany"].ToString();
                    recordModel.lastIndusty = row["lastIndusty"].ToString();
                    recordModel.major = row["major"].ToString();
                    recordModel.school = row["school"].ToString();
                    recordModel.highestDegree = row["highestDegree"].ToString();
                    listRequest.Add(recordModel);
                }
                GetPersonWorkHistory(ref listRequest);
                return listRequest;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        private bool GetPersonWorkHistory(ref List<PersonModel> listRequest)
        {
            try
            {
                for (int i = 0; i < listRequest.Count; i++)
                {
                    string selectSqlstr = "select * from WorkHistory  where personId = '" + listRequest[i].guid + "'; ";
                    DataSet ds = SqlLiteLongConn.Query(selectSqlstr);
                    if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                        continue;

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        WorkHistoryModel recordModel = new WorkHistoryModel();
                        recordModel.guid = row["guid"].ToString();
                        recordModel.personId = row["personId"].ToString();
                        recordModel.startTime = row["startTime"].ToString();
                        recordModel.endTime = row["endTime"].ToString();
                        recordModel.position = row["position"].ToString();
                        recordModel.department = row["department"].ToString();
                        recordModel.company = row["company"].ToString();
                        recordModel.industy = row["industy"].ToString();
                        recordModel.workDescription = row["workDescription"].ToString();
                        recordModel.order = int.Parse(row["orderNum"].ToString());
                        listRequest[i].workHistoryModels.Add(recordModel);
                    }
                }

                return true;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        
        
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <returns></returns>
        public bool DeletePerson(string id)
        {
            string deleteStr = "delete  from PersonInfo where guid ='" + id + "'";
            try
            {
                int result = SqlLiteLongConn.ExecuteSql(deleteStr);
                if (result <= 0)
                {
                    //LogHelper.Write("删除本地缓存失败,sql：" + deleteStr);
                    return false;
                }
                else
                {
                    return DeleteWorkHistory(id);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        private bool DeleteWorkHistory(string personId)
        {
            string deleteStr = "delete  from WorkHistory where personId ='" + personId + "'";
            try
            {
                int result = SqlLiteLongConn.ExecuteSql(deleteStr);
                if (result <= 0)
                {
                    return false;
                }
                else
                {

                }
                return result > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public PersonModel GetPersonByGuid(string guid)
        {
            string selectSqlstr = "select * from PersonInfo where guid = '" + guid + "';";
            try
            {
                List<PersonModel> listRequest = new List<PersonModel>();
                DataSet ds = SqlLiteLongConn.Query(selectSqlstr);
                if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                    return null;
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    PersonModel recordModel = new PersonModel();
                    recordModel.guid = row["guid"].ToString();
                    recordModel.name = row["name"].ToString();
                    recordModel.cardNo = row["cardNo"].ToString();
                    recordModel.phone = row["phone"].ToString();
                    recordModel.email = row["email"].ToString();
                    recordModel.gender = row["gender"].ToString();
                    recordModel.birth = row["birth"].ToString();
                    recordModel.address = row["address"].ToString();
                    recordModel.photo = row["photo"].ToString();
                    recordModel.height = row["height"].ToString();
                    recordModel.lastPosition = row["lastPosition"].ToString();
                    recordModel.lastCompany = row["lastCompany"].ToString();
                    recordModel.lastIndusty = row["lastIndusty"].ToString();
                    recordModel.major = row["major"].ToString();
                    recordModel.school = row["school"].ToString();
                    recordModel.highestDegree = row["highestDegree"].ToString();
                    listRequest.Add(recordModel);
                }
                GetPersonWorkHistory(ref listRequest);
                return listRequest[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }

}
